// Copyright 2022 The Molego Authors. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.

package translate

import (
	"strings"

	. "gitee.com/west0207/molego/core/log"
	utils "gitee.com/west0207/molego/core/utils"

	etree "gitee.com/west0207/etree"
)

// <addColumn tableName="sum_user_extend">
// 	<column name="first_login_time" type="varchar(30)" remarks="first login time, yyyy-MM-dd HH:mm:ss" />
// 	<column name="login_count" type="decimal(7)" remarks="login count" />
// </addColumn>
// 获取添加表字段的Entity标签的sql语句
// dbmsName *string 驱动名称，例如：mysql
// addColumnEle *etree.Element 添加表字段的Entity标签
func GetAddColumnSql(dbmsName *string, addColumnEle *etree.Element) (string, error) {
	return getDefaultAddColumnSql(dbmsName, addColumnEle)
}

// [mysql / H2]
// alter table sum_api add new_col0 varchar(20);
// alter table sum_api add new_col1 varchar(20) comment '新列1';
// alter table sum_api add new_col2 varchar(20) not null comment '新列2';
// alter table sum_api add new_col3 varchar(20) not null default '默认值1' comment '新列3';
// alter table sum_api add new_col4 varchar(20) default '默认值2' comment '新列4';
// alter table sum_api add new_col5 decimal(5) not null;
// create unique index uk_user_login_account on test_t04 (login_account);
//
// [sqlite]
// -- 添加新列不支持注释
// -- 非空字段(not null)必须提供默认值
// alter table t_table_user add new_col4 decimal(5);
// alter table t_table_user add new_col3 varchar(20) not null default '你好';
// alter table t_table_user add new_col5 decimal(5) not null default 6;
// create unique index uk_user_login_account on test_t04 (login_account);
//
// [kingbase,postgres]
// alter table t_table_user add new_col4 varchar(100) default 'def value';
// comment on column t_table_user.new_col4 is 'comment new_col4';
//
// [mssql]
// alter table t_table_user add new_col4 varchar(100) default 'def value';
// execute sp_addextendedproperty 'MS_Description','executed time','user','dbo','table','db_change_file','column','executed_time';
//
// <addColumn tableName="t_table_user">
// 	<column name="first_login_time" type="varchar(30)" remarks="首次登录时间, yyyy-MM-dd HH:mm:ss" />
// 	<column name="login_count" type="decimal(7)" defaultValue="0" remarks="成功登录次数">
// 		<constraints nullable="false"/>
// 	</column>
// 	<column name="login_account" type="varchar(20)" defaultValue="默认值" remarks="登录账号">
// 		<constraints unique="true" nullable="false" uniqueConstraintName="uk_user_login_account" />
// 	</column>
// </addColumn>
func getDefaultAddColumnSql(dbmsName *string, addColumnEle *etree.Element) (string, error) {
	tableName := addColumnEle.SelectAttrValue("tableName", utils.EMPTY)
	SetPropertyValue(dbmsName, &tableName)
	uniqueKeys := make([]string, 0, 1)
	var builder strings.Builder
	columnElements := addColumnEle.SelectElements("column")
	columnLen := len(columnElements)
	builder.Grow(columnLen * utils.ONE_HUNDRED)
	var columnElem *etree.Element
	for i := 0; i < columnLen; i++ {
		columnElem = columnElements[i]
		cname := columnElem.SelectAttrValue("name", utils.EMPTY)
		SetPropertyValue(dbmsName, &cname)
		ctype := columnElem.SelectAttrValue("type", utils.EMPTY)
		SetPropertyValue(dbmsName, &ctype)
		SetColumnType(dbmsName, &ctype)

		defaultValue := columnElem.SelectAttrValue("defaultValue", utils.EMPTY)
		// alter table sum_api add new_col1 varchar(20) not null default '默认值1' comment '新列1';
		utils.StringAppender(&builder, "alter table ", tableName, " add ", cname, utils.SPACE, ctype)
		if *dbmsName == Oracle && defaultValue != utils.EMPTY {
			// Oracle null/not null必须在default后面
			// alter table sum_api add new_col1 varchar(20) default '默认值1' not null comment '新列1';
			SetPropertyValue(dbmsName, &defaultValue)
			utils.StringAppender(&builder, GetDefaultValueClause(&defaultValue, &ctype))
		}
		constraintsElem := columnElem.SelectElement("constraints")
		if constraintsElem != nil {
			nullable := constraintsElem.SelectAttrValue("nullable", utils.TRUE)
			if nullable == utils.FALSE {
				if *dbmsName == "sqlite" && defaultValue == utils.EMPTY {
					// sqlite数据库要求非空字段必须提供默认值
					Sug.Errorf("sqlite数据库要求非空字段必须提供默认值: %v.%v", tableName, cname)
					panic("sqlite数据库要求非空字段必须提供默认值")
				}
				utils.StringAppender(&builder, " not null")
			}
			unique := constraintsElem.SelectAttrValue("unique", utils.FALSE)
			uniqueConstraintName := constraintsElem.SelectAttrValue("uniqueConstraintName", utils.EMPTY)
			SetPropertyValue(dbmsName, &uniqueConstraintName)
			if unique == utils.TRUE {
				// create unique index uk_test_role_uuid on test_role (uuid);
				if uniqueConstraintName == utils.EMPTY {
					uni := tableName + cname
					sum := utils.Sha256(&uni)
					ukname := sum[1:20]
					uniqueKeys = append(uniqueKeys, "create unique index uk_"+ukname+" on "+tableName+" ("+cname+");")
				} else {
					uniqueKeys = append(uniqueKeys, "create unique index "+uniqueConstraintName+" on "+tableName+" ("+cname+");")
				}
			}
		}
		if *dbmsName != Oracle && *dbmsName != MsSQLServer && defaultValue != utils.EMPTY {
			// Oracle已经添加，MsSQLServer需要在后面单独添加
			SetPropertyValue(dbmsName, &defaultValue)
			utils.StringAppender(&builder, GetDefaultValueClause(&defaultValue, &ctype))
		}

		cremarks := columnElem.SelectAttrValue("remarks", utils.EMPTY)
		cremarks = strings.ReplaceAll(cremarks, utils.SINGLE_QUOTE, utils.EMPTY)
		if utils.EMPTY != cremarks {
			// 有字段注释
			switch *dbmsName {
			case SQLite:
				// sqlite数据库添加新列不支持注释
				utils.StringAppender(&builder, ";", utils.LF)
			case Kingbase, PostgreSQL, Dameng, Oracle:
				// alter table test_t04 add first_login_time varchar(30);
				// comment on column test_t04.first_login_time is '首次登录时间, yyyy-MM-dd HH:mm:ss';
				utils.StringAppender(&builder, ";", utils.LF)
				utils.StringAppender(&builder, "comment on column ", tableName, ".", cname, " is '", cremarks, "';", utils.LF)
			case MsSQLServer:
				// alter table test_t04 add first_login_time varchar(30);
				// execute sp_addextendedproperty 'MS_Description','executed time','user','dbo','table','db_change_file','column','executed_time';
				utils.StringAppender(&builder, ";", utils.LF)
				utils.StringAppender(&builder, "execute sp_addextendedproperty 'MS_Description','", cremarks, "','user','dbo','table','", tableName, "','column','", cname, "';", utils.LF)
			default:
				// mysql,h2,tidb
				utils.StringAppender(&builder, " comment '", cremarks, "';", utils.LF)
			}
		} else {
			utils.StringAppender(&builder, ";", utils.LF)
		}

		if *dbmsName == MsSQLServer && defaultValue != utils.EMPTY {
			// MsSQLServer需要单独添加默认值约束
			// alter table test_role add constraint dfc_test_role_update_user default '20000' for update_user;
			utils.StringAppender(&builder, "alter table ", tableName, " add constraint dfc_",
				tableName, "_", cname, GetDefaultValueClause(&defaultValue, &ctype),
				" for ", cname, ";", utils.LF)
		}
	}

	// create unique index uk_test_role_uuid on test_role (uuid);
	for _, uniqueKey := range uniqueKeys {
		utils.StringAppender(&builder, uniqueKey, utils.LF)
	}

	return builder.String(), nil
}
